In this blog we will learn how to get a last refresh date in Microsoft Power BI report. So, using this practice, we will know last data refresh and also knows the problem with dataset refresh. In order to get latest refresh dates, you have to follow the below steps. Step 1: Open your report in “Power BI Desktop”. Step 2: Click on Transform data, it will open Power query Step 3: Click on New Source, select Blank query. Step 4: Enter DAX expression: = DateTime.LocalNow() and also change the name Step 5: Before use its need to convert into To Table. Step 6: Rename the column name like DateTime Step 7: Click on Close & Apply Step 8: Drag the Datetime field in report and apply card level visualization. It will work when you refresh the whole model, it will not refresh the date when individual table refresh. Hope this helps!

In this blog we will learn how to hide the filter panel in Microsoft Power BI report. Once you publish the Power BI report in Power BI service, filter panel is useless for the end user. In order to hide the filter pane, you have to follow the below steps. Step 1: Open your report in “Power BI Desktop”. Step 2: Click on filter pane. Step 3: There is a eyeball icon at the top of the filter pane that can be toggled. Save the report and publish to your workspace. Before published the report looks like below. After the published the report, user can not see the filter pane. You can also do this in the Edit Report in the service. Hope this helps!

In this blog we will learn how to append two or more table into new table or existing table. The Append Queries in Microsoft Power BI is an equivalent of UNION ALL in SQL. Consider two sample customer table; one for CustomerOne: And Customertwo: Open Microsoft Power BI for Desktop > Get Data > Excel > the excel file. You should see this: Select tables and click on Transform Data. Now it’s time to proceed with the Append operation itself: Click the little triangle on the main “Append Queries” button. You’ll get 2 options: Append Queries – this operation would add rows into an existing table Append Queries as New – this operation will create a new output table from 2 (or more) appended tables. I’m going for this option. So, let’s see what happens after clicking the “Append Queries as New” button: You either append 2 tables like me OR you can do “Three or more”. So, keep in mind you are NOT limited to 2 tables only. One important thing to understand how the tables are actually “appended” together. Power BI looks at column names. If it finds the same columns like in my case (CustomerID, First Name, Last Name, Contact No) in both the tables, it won’t create any new columns and it will fit everything right into those 4 columns. What about Duplicates? Append Queries will NOT remove duplicates. You have to use Group by or Remove Duplicate Rows to get rid of duplicates. So, you press OK and you get the result of your operation. Now the result: Hope this helps!

However, it seems there’s no “e-mail activity” in Azure Data Factory. I would like to send an e-mail notification if one of the activities fail. In this blog I am going to explain you how to send an e-mail notification using ADF Web Activity and Azure Logic App. Sending an Email with Logic Apps Logic Apps allows you to easily create a workflow in the cloud without having to write much code. First login to Azure.portal.com Choose to create a new resource, search Logic App Click on Create button, it will be asked to specify some details for the new Logic App: Click on Review + create again to finalize the creation of your new Logic App. After the app is deployed, you can find it in the resource menu. Click on the app to go the app itself. Click on Logic app designer link. In this tip, we need the HTTP request (“When a HTTP request is received”) as the trigger, since we’re going to use the Web Activity in ADF to start the Logic App. From ADF, we’re going to pass along some parameters in the HTTP request, which we’ll use in the e-mail later on. This can be done by sending JSON along in the body of the request. The following JSON schema is used: { “properties”: { “DataFactoryName”: {             “type”: “string”         }, “EmailTo”: {             “type”: “string”         }, “ErrorMessage”: {             “type”: “string”         }, “PipelineName”: {             “type”: “string”         }, “Subject”: {             “type”: “string”         } }, “type”: “object” } We’re sending the following information: The name of the data factory. Suppose we have a large environment with multiple instances of ADF. We would like to send which ADF has a pipeline with an error. The e-mail address of the receiver. An error messages. The name of the pipeline where there was an issue. The subject of the e-mail. In the editor, click on New step to add a new action to the Logic App: Click on New step it will send the e-mail. When you search for “mail” you will see there are may different actions: Click on Office 365 Outlook and select Send an email(V2) Once you’re logged in, you can configure the action. We’re are going to use dynamic content to populate some of the fields The result look like this: Once you click on Save, it will generate HTTP POST URL. Copy this URL for ADF. Triggering the Logic App from ADF Suppose you already created pipeline with some activities in ADF: Add a web activity to the canvas and connect another activity to this new activity using the arrow. When the connection has been made, right-click on the connection to change it to a Failure precedence constraint. This will change the color of the connector to red. Now we are using HTTP POST URL that will copied from the Azure Logic App We also need to add a header, where we will set the Content-Type to application/json. In the body, we enter the following JSON (following the structure mentioned before): { “DataFactoryName”: “@{pipeline().DataFactory}”, “PipelineName”: “@{pipeline().Pipeline}”, “Subject”: “An error has occurred!”, “ErrorMessage”: “The ADF pipeline has crashed! Please check the logs.”, “EmailTo”: “myemail@outlook.com” } We’re using system parameters to retrieve the name of the data factory and the name of the pipeline. All the other fields in the settings pane can be left as-is. Now we can run the pipeline and wait to see if any emails come in: I hope this will help you.

In this blog I am going to explain you how to create a Date table in Power BI desktop. A date table is a table that contains one column of DateTime type with unique values. It’s also called CALENDAR table A data model typically contains a date table A date table is designed to slice dates It should contain all dates included in the period to analyse It should contain one column of DateTime type Each day should be represented by a single a row There should be no gaps in the data It should be marked as a date table Using a date table with only the relevant year is best practise It’s recommended to have a rich set of additional columns Give the data model the flexibility to perform time intelligence calculations by using specific DAX functions Slice data by using one of its columns Create relationships between a date table and other tables in the data model Filter and group data across multiple tables Types of Date tables There are two ways to create a table Automatic Date Tables Power BI automatically creates a date table for each Date or DateTime column in the model. This makes it possible to slice each date by year, quarter, month and day. Calculated Date Tables It can be done by using two DAX functions: CALENDAR and CALENDARAUTO Syntax: Dates = CALENDAR(<StartDate>,<EndDate>) Dates = CALENDAR (      FIRSTDATE(Table[column]),    LASTDATE(Table[column])        ) Calendar functions return a table with a single column named “Date” and a list of values for the dates. Calendar requires the boundaries of the set of dates. CalendarAuto searches among all the dates in the data model and automatically finds the first and last year referenced within the model. Dates = CALENDARAUTO() The final formula for the calculated table should look something like this: Date = ADDCOLUMNS ( CALENDAR (DATE(2018,1,1), DATE(2025,12,31)), “DateAsInteger”, FORMAT ([Date], “YYYYMMDD”), “Year”, YEAR ([Date]), “Monthnumber”, FORMAT ([Date], “MM”), “YearMonthnumber”, FORMAT ([Date], “YYYY/MM”), “YearMonthShort”, FORMAT ([Date], “YYYY/mmm”), “MonthNameShort”, FORMAT ([Date], “mmm”), “MonthNameLong”, FORMAT ([Date], “mmmm”), “DayOfWeekNumber”, WEEKDAY ([Date]), “DayOfWeek”, FORMAT ([Date], “dddd”), “DayOfWeekShort”, FORMAT ([Date], “ddd”), “Quarter”, “Q” & FORMAT ([Date], “Q”), “YearQuarter”, FORMAT ([Date], “YYYY”) & “/Q” & FORMAT ([Date], “Q”) ) Once the date table is in place, it is a good idea to mark it as a date table as shown here: This both simplifies the code to author time intelligence calculations and automatically disables the auto Date/Time feature. I hope this will help you.  

In this blog I am going to explain you how to create a custom URL for your text column in Power BI desktop. You can create hyperlinks in tables and matrix in Power BI desktop. Before jumping to use a new feature in Power BI desktop, I am showing you how we had done previously. Step 1: Import sample data in Power BI desktop. Step 2: If the hyperlink doesn’t already exist as a field in your dataset, use Power BI Desktop to add it as custom column like “WebURL”. Step 3: In Data view, select the column and in the Modeling tab choose the dropdown for Data Category. Step 4: Select Web URL. Step 5: Switch to Report view and create a table or matrix using the field categorized as a Web URL. The hyperlinks will be blue and underlined. Step 5: If you don’t want to display a long URL in a table, you can display a hyperlink icon instead. Note that you can’t display icon in matrix. Select the table to make it active. Select the Format icon to open the Formatting tab. Expand Values, Iocate URL icon, and turn it on. Step 6: Now we want a web link on custom column, so first we uncategorized the WebURL column. In Data view, select the column and in the Modeling tab choose the dropdown for Data Category. Step 7: Switch to Report view and remove the WebURL column from the table. Select the table to make it active. Select the Format icon to open the Formatting tab. Expand Conditional formatting, select filed that you want to active hyperlink and locate Web URL, and turn it on. Step 8: Turn Web URL on, one popup will be appeared in this you have to select WebURL field and click on OK button. Step 9: Result look like below screenshot, now you hover on column field you can clearly see the hyperlink. I hope this will help you.  

In this blog I am going to explain you how to send an e-mail to any audience when a Power BI data alert is triggered. Step 1: Create a sample of Power BI report using visuals like gauge, KPI or card title. Step 2: Publish your Power BI report on app.powerbi.com site. Step 3: Pin individual visuals like gauge, KPI or card title. Step 4: Go to dashboard page, select chart and click on ellipsis Step 5: Click on Manage alerts, click on Add alert rule Here you can set the threshold condition and based on above and below amount. Also set maximum notification frequency either at most every 24 hours or at most once an hour. Step 5: Login to https://flow.microsoft.com/ Step 6: Create a Templates “Send an e-mail to any audience when a power BI data alert Is triggered” After clicking on continue button Set Alert Id that have been created on manage alert in Power BI. Click on Save button. Step 7: Whenever data changes on Power BI dataset and alert rule condition satisfied this Power Automate flow is triggered and send mail to target audiences. I hope this will help you.

This blog will explain how to use “What If” Parameter in Power BI desktop. Via What If parameter in Power Bi can easily give you the ability to dynamically transform your data. Using this parameter will allow to demonstrate how your data change under various scenarios. For example, how much revenue would you have if your products were at 5%, 10% or 15% of the retail price. Another scenario would be to show create a marketing mix to show how profit would change due to different investment in each channel and also if company increased or decreased its budget then how the revenue amount will be change etc. How to use What IF parameters in Power BI Step 1: Click the Modeling tab in the top ribbon. Step 2: Click the What IF parameter from the top ribbon. Step 3: The What If parameter window will open, provide details such as Name, Data Type, Minimum, Maximum and Default number. Step 4: Lastly, you can add an optional slicer. Step 5: A table with a calculated measure will be created A generated series that spans the specifications of your parameter. A selected value function that changes as the parameter changes. Let’s do it practically Scenario: You have a list of Azure usages details like server name, VM name and cost. Company would like to create a parameter that allows them to apply usage so that company can see the overall cost of each year when different usages are applied. Company would like to have a parameter that spans from 0% to 50% with a 5% increment. You can see how this parameter is created by viewing the new table: Once the What If parameter is created you have your generated series that looks like: Usages Percentage = GENERATESERIES(0, 0.5, 0.05) And you will have a selected value functions that looks like this: Usages Percentage Value = SELECTEDVALUE(‘Usages Percentage'[Usages Percentage],0) Both of these are automatically created for you. Apply the parameter to your data In this case, company want to apply the usage to total cost. This can be easily done with a calculated measure. Usages = SUM(AzureUsages[TotalCost])*’Usages Percentage'[Usages Percentage Value] The final result you can pull into a cluster column chart or table so that company can see how the cost is affected by the usage parameter as you slide it to different usage values. I hope this is helpful. Check out my other blog here https://www.cloudfronts.com/embed-secure-power-bi-report-using-python-web-application-with-flask-in-visual-studio-2015/

In today’s business intelligence world, Power BI has become a beloved among many. In this blog, I am going to cover Power BI best practices, that will assistance to you while developing the Power BI report. Company Logo Use the logo in their background. Data Timestamp to show, when it was refreshed last We can implement it, to show the end user, when the data was refreshed last. Less use of scrollable Page End users always feel difficulty when the report has a scrollable page. We do not recommend making the page scrollable if not necessary. Instead of making page scrollable, use Bookmark and Selection Pane. Census dashboard doesn’t have a scrollable page which is good. Use Basic Reporting Filter Power BI provides the following type of filters. Visual Level Page Level Report Level Use the filters wisely as per your requirement. Pull data from views, not tables Importing data from tables in a SQL Server, MY SQL or Oracle database creates strong dependencies between the physical data model and the reporting engine. Whenever table structures change, it’s best to pull relational data from views. Filter before import If you’re importing data into Power BI instead of a live connection, it’s best to limit the amount of processes that happen inside the tool. Power BI has a limit for the amount of data that’s allowed to be imported, so any steps to avoid reaching that limit will be a plus. Narrow tables are faster than short and wide tables If the performance is slowing down as you’re adding data, it’s mostly due to wide tables.  Power BI reacts much faster using narrow and long tables versus short and wide ones. Remove unused fields Whenever adding more and more data, you will notice the pbix file size increasing.  One of the best and quickest ways to reduce the pbix file is to remove any unused fields. How: Click Edit Query > then select the table you want to remove the fields from > Click Choose Columns. Label all of your steps As you’re going through and modifying the imported data, Power BI creates a history that allows you to seamlessly go back and remove any changes that might break the datasets.  Furthermore, labelling each of these steps allows you to easily remember what each does. Limit the visuals in dashboards and reports The Microsoft Power BI performance best practices highlight that placing many visuals in a single report is responsible for it. This is what you need to do in order to limit the number of visuals in dashboards and reports: Limit to a minimum of eight widget visuals in every report page and keep the grids to a minimum of one in every page The pages should be limited to no more than 30 points (cards: 1, gauges: 2, charts: 3, maps: 3, grids: 5) Keep the tiles limited to no more than 10 per dashboard. Remove unnecessary interactions between visuals Do you know the secret of improving Power BI report performance? Here’s a clue! You can make that possible by removing unnecessary interactions between visuals. This is possible because of the reason that all visuals on a report can interact with one another by default. The interactivity should be controlled and modified for optimal performance. Further, you can reduce the number of queries fired at the backend and improve report performance by disabling unwanted interactivity. Enable Row-Level Security (RLS) Power BI only imports the data that the user is authorized to view, with RLS that restricts user access to certain rows in a database depending on the characteristics of the user executing a query. But how to attain substantial performance gains? You can enable this by combining Power BI roles with roles in the backend. Moreover, you need to test all roles prior to rolling out to production. Use Microsoft AppSource certified custom visuals The Power BI certified custom visuals are verified by Microsoft to have robust as well as well-performing code. These AppSource visuals have passed rigorous quality testing and are the only custom visuals that can be viewed in Export to PowerPoint and email subscriptions. Avoid using hierarchical filters We recommend, not to use any hierarchical slicers in the report. Currently, we have observed that no hierarchical slicers are used in the report. Experience an enhanced performance in Power BI by using multiple filters for the hierarchy. Categorize the data for Power BI reports One of the best practices in Power BI is to provide data categorization for the Power BI reports (HBI, MBI, LBI). The Power BI data classification enables you to raise user awareness about the security level that is required to be used. This also helps you to understand the way reports should be shared inside as well as outside the organization. The categories can be listed as: HBI or High Business Impact data, that requires users to get a policy exception to share the data eternally. LBI or Low Business Impact as well as MBI or Medium Business Impact, that do not require any exceptions. Use the On-premises data gateway It is suggestible as well as one of the best practices to use on-premises data gateway instead of Personal Gateway for it takes data and imports it into Power BI. But why Enterprise Gateway? It is more efficient while you work with large databases as Enterprise Gateway imports nothing. Use separate Power BI gateways for “Direct Query” and “Scheduled Refresh” Using the same gateway for Scheduled Data Refresh and Live Connection slows down the Live Connection performance when the Scheduled Data Refresh is active. It is suggestible for you to create separate gateways for Live Connection and Scheduled Refresh to avoid such issues. Test each custom visual on a report for ensuring fast report load time The Power BI team doesn’t thoroughly test the custom visuals that are not certified. So, while handling large datasets or complex aggregations, the custom visuals might perform poorly. What should you do when the chosen visual … Continue reading Top 20 Best Practices of Power BI →

In this article, we will create a Gantt Chart or Progress bar inside a table in SSRS . In SQL Server Reporting Service does not allow a report programmer to modify a “width” property at run time. The challenging part of this task is how to create a scale and how to create a progress bar that spans a start date and end date. But SSRS have a range bar chart that will help you to make a Gantt chart or progress bar. And display diamond shape if start date and end date is same. For creating Gantt chart, we need a Data source, a Dataset, a Table and a Range Bar chart. Steps Step 1: First create a new data source. Step 2:  Before creating a data set, we need to write a Fetch XML query that created the data we’re going to work with. Fetch XML Query: <fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”> <entity name=”milestone”> <attribute name=”createdon” /> <attribute name=”item” /> <attribute name=”projectid” /> <attribute name=”contractorid” /> <attribute name=”packageid” /> <attribute name=”startdatemilestone” /> <attribute name=”remarks” /> <attribute name=”finishdatemilestone” /> <attribute name=”startdateforecast” /> <attribute name=”finishdateforecast” /> <attribute name=”startdateactual” /> <attribute name=”finishdateactual” /> <attribute name=”progress” /> <attribute name=”status” /> <attribute name=”durationdays” /> <attribute name=”sequencenumber” /> <attribute name=”milestoneid” /> <order attribute=”item” descending=”false” /> <filter type=”and”> <condition attribute=”projectid” operator=”eq” uiname=”ABC” uitype=”pmtracker” value=”{86AF05D9-6962-E911-A834-000D3A07F3D7}” /> </filter> </entity> </fetch> This gives us a result set like this : Sequence Number Item Package Start Date(Milestone) Finish Date(Milestone) Start Date(Forecast) Finish Date(Forecast) Start Date(Actual) Finish Date(Actual) Duration(Days) Status 1 Concept Design Lighting 02-01-2019 06-02-2019 03-01-2019 09-02-2019 03-01-2019 09-02-2019 37.00 Completed 2 Concept Design approval Lighting 13-02-2019 13-02-2019 07-02-2019 14-02-2019 07-02-2019 13-02-2019 6.00 Completed 3 Detail Design Lighting 14-02-2019 17-04-2019 14-02-2019 17-02-2019 14-02-2019 18-02-2019 4.00 Completed 4 Detail Design Approval Lighting 17-04-2019 23-04-2019 17-04-2019 24-04-2019 17-04-2019 22-04-2019 Delay 5 Tender Issue Lighting 29-05-2019 31-05-2019 01-06-2019 03-06-2019 01-06-2019 03-06-2019 Delay 6 Tender Return Lighting 31-05-2019 22-06-2019 03-06-2019 03-06-2019 03-06-2019 24-06-2019 21.00 Completed Step 3: Now we create a Data set from this query: If start date and end date is same then need to display diamond shape, for that we have to add three calculated field. Need to find number of days between two dates. DayDiff_MileStone=DateDiff(“d”,Fields!startdatemilestoneValue.Value,Fields!finishdatemilestoneValue.Value) we are going to set the following properties: We’re going to create a table within the report that has a column for each of the columns in the data set, plus a column for a graph. We just have to bind the Table to the data set “MilstoneData” Then drag the columns of the data set to the columns of the table. Step 4 : Build the Gantt Chart SSRS have the Range Bar Chart option. Just drop the chart onto a blank area of the report. We’ll fit it into table later. Now we are going to take fewer part of the chart. We’re throw away of: The chart title The legend The vertical Axis title The vertical axis The horizontal axis title When we’re done, the chart should look like this: Let’s start setting up the chart. The first thing we need to do is set the Minimums, Maximums, Intervals and Interval Types. The client wants minimum date must be from “startdatemilestone” date and maximum date should be “finishdateforecast” date. On the Horizontal Axis, we are going to set the following properties: Minimum =DateSerial(Year(Min(Fields!startdatemilestoneValue.Value, “MilestoneData”)), Month(Min(Fields!startdatemilestoneValue.Value, “MilestoneData”)), -1) Maximum =DateSerial(Year(Max(Fields!finishdateforecastValue.Value, “MilestoneData”)), Month(Max(Fields!finishdateforecastValue.Value, “MilestoneData”)) , 0) Interval 1 Interval Type Months Also going to set the format the of items along the Horizontal Axis to MM-yyyy Go to chart series properties, set Marker properties. Let’s get a preview of the report and see how we’re doing. It’s correct, but it doesn’t provide the milestone data. Now cut the chart and paste it into the details row of the Table, in the right most column. Now we’re going to run a preview. OOPS! We receive the error “The chart has a detail member with inner members. Detail members can only contain static members. What this means is that chart cannot live in a detail row. We’re going to create a Group that contains only a single Item. After selecting the Table, at the bottom left of the screen, we find “Row Groups”, Just click on preview shows us: Nice-looking but do we really need a timeline on every now? Now we are going to need a timeline. So, let’s take a copy of the Chart and put it right next to the header “Duration” Now we can hide the Axis Labels on the chart in the footer. We can also change the Axis line style to none. Click on preview Wow! Hope this helps you! Check out my other blog here https://www.cloudfronts.com/performing-update-operation-in-microsoft-dynamics-nav-through-integration-in-scribe-online/